﻿// Dùng tại:
//      + frmDMCanBo

using System;
using System.Data;
using System.Data.SqlClient;

namespace prjKQXS_DLL.Danh_muc
{
    public class clsDMCanBo
    {
        /// <summary>
        /// Cài đặt event OnError để bắt các lỗi chưa được xử lý
        /// </summary>
        /// <param name="e"></param>
        public delegate void OnErrorHandler(Exception e);
        public event OnErrorHandler OnError;

        /// <summary>
        /// Cài đặt event OnAction để thông báo các trạng thái kết quả
        /// </summary>
        /// <param name="e"></param>
        public delegate void OnActionHandler(SqlException e);
        public event OnActionHandler OnAction;

        #region Methods ==============================================================
        /// <summary>
        /// Tự động sinh mã cán bộ theo mã đơn vị truyền vào
        /// </summary>
        /// <param name="MaDV">Mã đơn vị</param>
        /// <returns></returns>
        public string generateMaCB(string MaDV)
        {
            string ma = "0";

            if (clsLicense.checkLicense())
            {
                string c = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ";

                try
                {
                    DataTable dt = clsCommon.FillData("SELECT RIGHT(Ma_DT,1) AS MaCB FROM DMCANBO WHERE LEFT(Ma_DT, 1)='" +
                                    MaDV + "' ORDER BY RIGHT(Ma_DT,1)");
                    if (dt.Rows.Count > 0)
                    {
                        string s = "";
                        for (int i = 0; i < dt.Rows.Count; i++)
                            s += dt.Rows[i]["MaCB"].ToString().Trim().ToUpper();

                        for (int i = 0; i <= c.Length - 1; i++)
                            if (!s.Contains(c[i].ToString().ToUpper()))     //Nếu tập hợp mã đã có không chứa c[i]...
                            {
                                ma = c[i].ToString();   //.. thì lấy c[i] làm mã cho newrecord
                                break;
                            }
                    }
                }
                catch (SqlException e)
                {
                    OnAction(e);
                }
                catch (Exception e)
                {
                    OnError(e);
                }

                ma = MaDV + ma;
            }

            return ma;
        }

        #region Nhóm hàm kiểm tra trùng
        public static bool checkMaCB(string macb)   //overload này dùng kiểm tra khi thêm mới
        {
            bool ok = true;

            string sql = "SELECT Ma_DT FROM DMCANBO WHERE Ma_DT = '" + macb + "' and Ngay_ThanhLy is null";
            DataTable dt = clsCommon.FillData(sql);
            if (dt.Rows.Count > 0) ok = false;

            return ok;
        }

        public static bool checkMaCB(string macb, int id, bool isThanhLy = false)   //overload này dùng kiểm tra khi sửa
        {
            bool ok = true;

            string sql = "SELECT Ma_DT FROM DMCANBO WHERE Ma_DT = '" + macb + "' and Ngay_ThanhLy is null and ID<>" + id;
            DataTable dt = clsCommon.FillData(sql);

            if (isThanhLy)
            {
                if (dt.Rows.Count > 1) ok = false;
            }
            else
            {
                if (dt.Rows.Count > 0) ok = false;
            }

            return ok;
        }

        public static bool checkMST(string mst) //check for addnew
        {
            return !clsCommon.isDuplicate("MST", mst, "DMCANBO");
        }

        public static bool checkMST(string mst, int id) //check for edit
        {
            return !clsCommon.isDuplicate("MST", mst, "DMCANBO", id);
        }

        public static bool checkCMT(string cmt) //check for addnew
        {
            return !clsCommon.isDuplicate("CMT", cmt, "DMCANBO");
        }

        public static bool checkCMT(string cmt, int id) //check for edit
        {
            return !clsCommon.isDuplicate("CMT", cmt, "DMCANBO", id);
        }

        public static bool checkSoHD(string sohd) //check for addnew
        {
            return !clsCommon.isDuplicate("SoHD", sohd, "DMCANBO");
        }

        public static bool checkSoHD(string sohd, int id) //check for edit
        {
            return !clsCommon.isDuplicate("SoHD", sohd, "DMCANBO", id);
        }
        #endregion

        /// <summary>
        /// Lấy dữ liệu để nạp cho gv, gồm các cột:
        /// <para>CB.ID, CB.Ma_DT, CB.Ten_DT, Ma_DMChucVu AS MaCV, Ma_DMDonVi, DV.Ten_DT as TenDV, Tuyen, CB.SDT, isBanRong, </para>
        /// <para>Noi_O, SoHD, NgayHD, MST, Tien_Goc as Tien, CMT, Noi_Cap, Ngay_Cap, Thanh_Ly, Ngay_ThanhLy, Ly_Do, KeHoachGiao</para>
        /// </summary>
        /// <param name="madv">Mã đơn vị</param>
        /// <param name="KP">Chế độ khôi phục</param>
        /// <param name="TL">Chế độ lọc đang hoạt động/thanh lý/tất cả</param>
        /// <returns></returns>
        public DataTable loadData(string madv, bool KP, int TL)
        {
            DataTable dt = new DataTable();

            if (clsLicense.checkLicense())
            {
                string sql = "proc_getAllCanBo";
                try
                {
                    SqlConnection conn = new SqlConnection(BienTc.connString);
                    SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    da.SelectCommand.Parameters.AddWithValue("@isThanhLy", TL);

                    if (madv == "TC")
                    {
                        da.SelectCommand.Parameters.AddWithValue("@isTC", 1);
                        da.SelectCommand.Parameters.AddWithValue("@MaDV", "");  //Tham số giả

                        if (KP) da.SelectCommand.Parameters.AddWithValue("@isKhoiPhuc", 1);
                        else da.SelectCommand.Parameters.AddWithValue("@isKhoiPhuc", 0);

                    }
                    else
                    {
                        da.SelectCommand.Parameters.AddWithValue("@isTC", 0);
                        da.SelectCommand.Parameters.AddWithValue("@MaDV", madv);

                        if (KP) da.SelectCommand.Parameters.AddWithValue("@isKhoiPhuc", 1);
                        else da.SelectCommand.Parameters.AddWithValue("@isKhoiPhuc", 0);

                    }
                    da.Fill(dt);
                }
                catch (SqlException e)
                {
                    OnAction(e);
                }
                catch (Exception e)
                {
                    OnError(e);
                }
            }

            return dt;
        }

        public void Insert(DataRow dr, SqlConnection connection = null)
        {
            if (clsLicense.checkLicense())
            {
                try
                {
                    SqlConnection conn = new SqlConnection(BienTc.connString);
                    conn.Open();

                    SqlCommand cmd = new SqlCommand("proc_DMCanBo_Insert", conn);
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.AddWithValue("@Ma_DT", dr["Ma_DT"].ToString().Trim().ToUpper());
                    cmd.Parameters.AddWithValue("@Ten_DT", dr["Ten_DT"]);
                    cmd.Parameters.AddWithValue("@SDT", dr["SDT"]);
                    cmd.Parameters.AddWithValue("@Ma_DMChucVu", dr["MaCV"]);
                    cmd.Parameters.AddWithValue("@Ma_DMDonVi", dr["Ma_DMDonVi"]);
                    cmd.Parameters.AddWithValue("@SoHD", dr["SoHD"]);
                    cmd.Parameters.AddWithValue("@NgayHD", dr["NgayHD"]);
                    cmd.Parameters.AddWithValue("@Noi_O", dr["Noi_O"]);
                    cmd.Parameters.AddWithValue("@MST", dr["MST"]);
                    cmd.Parameters.AddWithValue("@CMT", dr["CMT"]);
                    cmd.Parameters.AddWithValue("@Noi_Cap", dr["Noi_Cap"]);
                    cmd.Parameters.AddWithValue("@Ngay_Cap", dr["Ngay_Cap"]);
                    cmd.Parameters.AddWithValue("@Tuyen", dr["Tuyen"]);
                    cmd.Parameters.AddWithValue("@isBanRong", dr["isBanRong"]);

                    if (dr["Tien"] == DBNull.Value) 
                        cmd.Parameters.AddWithValue("@Tien_Goc", 0);
                    else 
                        cmd.Parameters.AddWithValue("@Tien_Goc", dr["Tien"]);

                    cmd.Parameters.AddWithValue("@Ngay_ThanhLy", dr["Ngay_ThanhLy"]);
                    cmd.Parameters.AddWithValue("@Ly_Do", dr["Ly_Do"]);
                    cmd.Parameters.AddWithValue("@KeHoachGiao", dr["KeHoachGiao"]);
                    int i = cmd.ExecuteNonQuery();
                }
                catch (SqlException e)
                {
                    OnAction(e);
                }
                catch (Exception e)
                {
                    OnError(e);
                }
            }
        }

        public void Update(DataRow dr)
        {
            if (clsLicense.checkLicense())
            {
                try
                {
                    SqlConnection conn = new SqlConnection(BienTc.connString);
                    conn.Open();

                    SqlCommand cmd = new SqlCommand("proc_DMCanBo_Update", conn);
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.AddWithValue("@ID", dr["ID"].ToString().Trim().ToUpper());
                    cmd.Parameters.AddWithValue("@Ma_DT", dr["Ma_DT"].ToString().Trim().ToUpper());
                    cmd.Parameters.AddWithValue("@Ten_DT", dr["Ten_DT"]);
                    cmd.Parameters.AddWithValue("@SDT", dr["SDT"]);
                    cmd.Parameters.AddWithValue("@Ma_DMChucVu", dr["MaCV"]);
                    cmd.Parameters.AddWithValue("@SoHD", dr["SoHD"]);
                    cmd.Parameters.AddWithValue("@NgayHD", dr["NgayHD"]);
                    cmd.Parameters.AddWithValue("@Noi_O", dr["Noi_O"]);
                    cmd.Parameters.AddWithValue("@MST", dr["MST"]);
                    cmd.Parameters.AddWithValue("@CMT", dr["CMT"]);
                    cmd.Parameters.AddWithValue("@Noi_Cap", dr["Noi_Cap"]);
                    cmd.Parameters.AddWithValue("@Ngay_Cap", dr["Ngay_Cap"]);
                    cmd.Parameters.AddWithValue("@Tuyen", dr["Tuyen"]);
                    cmd.Parameters.AddWithValue("@isBanRong", dr["isBanRong"]);

                    if (dr["Tien"] == DBNull.Value) cmd.Parameters.AddWithValue("@Tien_Goc", 0);
                    else cmd.Parameters.AddWithValue("@Tien_Goc", dr["Tien"]);

                    cmd.Parameters.AddWithValue("@Ngay_ThanhLy", dr["Ngay_ThanhLy"]);
                    cmd.Parameters.AddWithValue("@Ly_Do", dr["Ly_Do"]);
                    cmd.Parameters.AddWithValue("@KeHoachGiao", dr["KeHoachGiao"]);
                    int i = cmd.ExecuteNonQuery();
                }
                catch (SqlException e)
                {
                    OnAction(e);
                }
                catch (Exception e)
                {
                    OnError(e);
                }
            }
        }

        public void Delete(string DMCanBoID, bool canRestore)
        {
            if (clsLicense.checkLicense())
            {
                try
                {
                    SqlConnection sqlconn = new SqlConnection(BienTc.connString);
                    sqlconn.Open();
                    SqlCommand cmd;

                    if (canRestore)
                    {
                        cmd = new SqlCommand("UPDATE DMCANBO SET isPublic = 0 Where ID = '" + DMCanBoID + "' and isPublic=1", sqlconn);
                    }
                    else
                    {
                        cmd = new SqlCommand("DELETE DMCANBO WHERE ID = '" + DMCanBoID + "' and isPublic = 0", sqlconn);
                    }

                    int i = cmd.ExecuteNonQuery();
                }
                catch (SqlException e)
                {
                    OnAction(e);
                }
                catch (Exception e)
                {
                    OnError(e);
                }
            }
        }

        public void Restore(int id)
        {
            if (clsLicense.checkLicense())
            {
                try
                {
                    SqlConnection sqlconn = new SqlConnection(BienTc.connString);
                    sqlconn.Open();
                    SqlCommand cmd;

                    cmd = new SqlCommand("UPDATE DMCANBO SET isPublic = 1 Where ID = '" + id + "' and isPublic=0", sqlconn);
                    int i = cmd.ExecuteNonQuery();
                }
                catch (SqlException e)
                {
                    OnAction(e);
                }
                catch (Exception e)
                {
                    OnError(e);
                }
            }
        }
        #endregion ===================================================================
    }
}
